﻿using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml;

namespace HandleExcel
{
    public class HandleExcel
    {
        public static int SleepTime = int.Parse(ConfigurationManager.AppSettings["SleepTime"].ToString());
        public static string excelpath = ConfigurationManager.AppSettings["excelpath"];
        public static string newexcelpath = ConfigurationManager.AppSettings["newexcelpath"];
 
        public static string cn = ConfigurationManager.AppSettings["CN"];
        /// <summary>
        /// 获取所有二级目录下的excel文件
        /// </summary>
        /// <param name="path"></param>
        /// <param name="resultList"></param>
        /// <returns></returns>
        private static List<string> GetAllFiles(string path, List<string> resultList)
        {
            if (resultList == null)
            {
                resultList = new List<string>();
            }
            System.IO.DirectoryInfo di = new System.IO.DirectoryInfo(path);
            System.IO.FileInfo[] files = di.GetFiles();

            foreach (System.IO.FileInfo fi in files)
            {
                if ((fi.Extension.ToLower().Contains(".xlsx") || fi.Extension.ToLower().Contains(".xls")) && Path.GetFileNameWithoutExtension(fi.FullName).Contains("FORM"))
                {
                    resultList.Add(fi.FullName);
                }
            }

            DirectoryInfo[] dirinfo = di.GetDirectories();

            for (int i = 0; i < dirinfo.Length; i++)
            {
                GetAllFiles(dirinfo[i].FullName, resultList);
            }

            return resultList;
        }

        public static void HandleExcelContent()
        {
            //查询目录下的所有excel文件
            List<string> resultList = GetAllFiles(excelpath, null);
            //对数组进行升序排序
            var queryEnResultList = from n in resultList
                                    where !n.Contains(cn)
                                    orderby n
                                    select n;
            var queryCnResultList = from n in resultList
                                    where n.Contains(cn)
                                    orderby n
                                    select n;
            ////遍历所有的英文excel文件
            //foreach (var item in queryEnResultList)
            //{
            //    DataTable dt = ExcelToTable(item); 
            //    CreateEnXmlDoc(item, dt);
            //}
            //遍历所有的中文excel文件
            foreach (var item in queryCnResultList)
            {
                string json = ReadFromExcelFile(item);
                DataTable dt = JsonConvert.DeserializeObject<DataTable>(json);
                CreateCnXmlDoc(item, dt);
            }
        }

        public static void CreateEnXmlDoc(string filepath, DataTable dt)
        { 
            //首先创建 XmlDocument xml文档 
            XmlDocument xml = new XmlDocument();
            //创建Xml声明部分，即<?xml version="1.0" encoding="utf-8" ?>
            XmlDeclaration Declaration = xml.CreateXmlDeclaration("1.0", "utf-8", null);
            #region 节点
            //创建根节点 config           
            XmlNode rootCode = xml.CreateElement("Pisemnost");
            XmlAttribute verzeSW = xml.CreateAttribute("verzeSW");
            verzeSW.Value = "41.34.3";
            XmlAttribute nazevSW = xml.CreateAttribute("nazevSW");
            nazevSW.Value = "EPO MF ČR";
            rootCode.Attributes.Append(verzeSW);
            rootCode.Attributes.Append(nazevSW);
            XmlNode firstchild = xml.CreateElement("IRSPH1");
            XmlAttribute verzePis = xml.CreateAttribute("verzePis");
            verzePis.Value = "02.03";
            firstchild.Attributes.Append(verzePis);
            rootCode.AppendChild(firstchild);
            XmlNode VetaD = xml.CreateElement("VetaD");
            XmlAttribute dokument = xml.CreateAttribute("dokument");
            dokument.Value = "PH1";
            VetaD.Attributes.Append(dokument);
            XmlAttribute k_uladis = xml.CreateAttribute("k_uladis");
            k_uladis.Value = "IRS";
            VetaD.Attributes.Append(k_uladis);
            XmlAttribute typ_platce = xml.CreateAttribute("typ_platce");
            typ_platce.Value = "T";
            VetaD.Attributes.Append(typ_platce);
            firstchild.AppendChild(VetaD);

            XmlNode VetaP = xml.CreateElement("VetaP");
            XmlAttribute zkrobchjm = xml.CreateAttribute("zkrobchjm");
            zkrobchjm.Value = dt.Rows[1]["value"].ToString();
            VetaP.Attributes.Append(zkrobchjm);
            XmlAttribute c_pop = xml.CreateAttribute("c_pop");
            c_pop.Value = dt.Rows[6]["value"].ToString().Split(',')[0].Substring(0, 4);
            VetaP.Attributes.Append(c_pop);
            XmlAttribute c_ufo = xml.CreateAttribute("c_ufo");
            c_ufo.Value = "463";
            VetaP.Attributes.Append(c_ufo);
            XmlAttribute c_pracufo = xml.CreateAttribute("c_pracufo");
            c_pracufo.Value = "3201";
            VetaP.Attributes.Append(c_pracufo);
            XmlAttribute dic = xml.CreateAttribute("dic");
            dic.Value = "9999999999";
            VetaP.Attributes.Append(dic);
            XmlAttribute naz_obce = xml.CreateAttribute("naz_obce");
            naz_obce.Value = dt.Rows[6]["value"].ToString().Split(',')[2].Substring(1, dt.Rows[6]["value"].ToString().Split(',')[2].Length - 11);
            VetaP.Attributes.Append(naz_obce);
            XmlAttribute opr_jmeno = xml.CreateAttribute("opr_jmeno");
            opr_jmeno.Value = "Tianyou";
            VetaP.Attributes.Append(opr_jmeno);
            XmlAttribute opr_postaveni = xml.CreateAttribute("opr_postaveni");
            opr_postaveni.Value = "Zákonný zástupce pověřeného agent";
            VetaP.Attributes.Append(opr_postaveni);
            XmlAttribute opr_prijmeni = xml.CreateAttribute("opr_prijmeni");
            opr_prijmeni.Value = "Zhou";
            VetaP.Attributes.Append(opr_prijmeni);
            XmlAttribute psc = xml.CreateAttribute("psc");
            psc.Value = dt.Rows[6]["value"].ToString().Split(',')[2].Substring(dt.Rows[6]["value"].ToString().Split(',')[2].Length - 9).Substring(0, dt.Rows[6]["value"].ToString().Split(',')[2].Substring(dt.Rows[6]["value"].ToString().Split(',')[2].Length - 9).Length - 3);
            VetaP.Attributes.Append(psc);
            XmlAttribute stat = xml.CreateAttribute("stat");
            stat.Value = dt.Rows[6]["value"].ToString().Split(',')[2].Substring(dt.Rows[6]["value"].ToString().Split(',')[2].Length - 2, 2);
            VetaP.Attributes.Append(stat);
            XmlAttribute typ_ds = xml.CreateAttribute("typ_ds");
            typ_ds.Value = "P";
            VetaP.Attributes.Append(typ_ds);
            XmlAttribute ulice = xml.CreateAttribute("ulice");
            ulice.Value = dt.Rows[6]["value"].ToString().Split(',')[0].Substring(5, dt.Rows[6]["value"].ToString().Split(',')[0].Length - 5) + "," + dt.Rows[6]["value"].ToString().Split(',')[1];
            VetaP.Attributes.Append(ulice);
            XmlAttribute zast_ic = xml.CreateAttribute("zast_ic");
            zast_ic.Value = "9999999999";
            VetaP.Attributes.Append(zast_ic);
            XmlAttribute zast_kod = xml.CreateAttribute("zast_kod");
            zast_kod.Value = "4a";
            VetaP.Attributes.Append(zast_kod);
            XmlAttribute zast_nazev = xml.CreateAttribute("zast_nazev");
            zast_nazev.Value = "Beijing Tianyouyida Management Consulting Ltd Co";
            VetaP.Attributes.Append(zast_nazev);
            XmlAttribute zast_typ = xml.CreateAttribute("zast_typ");
            zast_typ.Value = "P";
            VetaP.Attributes.Append(zast_typ);

            firstchild.AppendChild(VetaP);

            XmlNode VetaA = xml.CreateElement("VetaA");
            XmlAttribute naz_ul = xml.CreateAttribute("naz_ul");
            naz_ul.Value = dt.Rows[6]["value"].ToString().Split(',')[0].Substring(5, dt.Rows[6]["value"].ToString().Split(',')[0].Length - 5) + "," + dt.Rows[6]["value"].ToString().Split(',')[1];
            VetaA.Attributes.Append(naz_ul);


            XmlAttribute c_dom = xml.CreateAttribute("c_dom");
            c_dom.Value = dt.Rows[6]["value"].ToString().Split(',')[0].Substring(0, 4);
            VetaA.Attributes.Append(c_dom);

            XmlAttribute naz_obce1 = xml.CreateAttribute("naz_obce");
            naz_obce.Value = dt.Rows[6]["value"].ToString().Split(',')[2].Substring(1, dt.Rows[6]["value"].ToString().Split(',')[2].Length - 11);
            VetaA.Attributes.Append(naz_obce1);

            XmlAttribute psc1 = xml.CreateAttribute("psc");
            psc1.Value = dt.Rows[6]["value"].ToString().Split(',')[2].Substring(dt.Rows[6]["value"].ToString().Split(',')[2].Length - 9).Substring(0, dt.Rows[6]["value"].ToString().Split(',')[2].Substring(dt.Rows[6]["value"].ToString().Split(',')[2].Length - 9).Length - 3);
            VetaA.Attributes.Append(psc1);

            XmlAttribute stat1 = xml.CreateAttribute("stat");
            stat1.Value = dt.Rows[6]["value"].ToString().Split(',')[2].Substring(dt.Rows[6]["value"].ToString().Split(',')[2].Length - 2, 2);
            VetaA.Attributes.Append(stat1);

            XmlAttribute email = xml.CreateAttribute("email");
            email.Value = " ";
            VetaA.Attributes.Append(email);

            XmlAttribute c_telef = xml.CreateAttribute("c_telef");
            c_telef.Value = " ";
            VetaA.Attributes.Append(c_telef);
            firstchild.AppendChild(VetaA);



            XmlNode VetaU = xml.CreateElement("VetaU");
            XmlAttribute poradi_uctu = xml.CreateAttribute("poradi_uctu");
            poradi_uctu.Value = "1";
            VetaU.Attributes.Append(poradi_uctu);


            XmlAttribute typ_uctu = xml.CreateAttribute("typ_uctu");
            typ_uctu.Value = "Z";
            VetaU.Attributes.Append(typ_uctu);

            XmlAttribute c_uctu = xml.CreateAttribute("c_uctu");
            c_uctu.Value = dt.Rows[14]["value"].ToString();
            VetaU.Attributes.Append(c_uctu);

            XmlAttribute mena = xml.CreateAttribute("mena");
            mena.Value = dt.Rows[15]["value"].ToString();
            VetaU.Attributes.Append(mena);

            XmlAttribute typ_idbank = xml.CreateAttribute("typ_idbank");
            typ_idbank.Value = "SC";
            VetaU.Attributes.Append(typ_idbank);

            XmlAttribute id_bank = xml.CreateAttribute("id_bank");
            id_bank.Value = dt.Rows[13]["value"].ToString();
            VetaU.Attributes.Append(id_bank);

            XmlAttribute verejny = xml.CreateAttribute("verejny");
            verejny.Value = "N";
            VetaU.Attributes.Append(verejny);

            XmlAttribute nazev_uctu = xml.CreateAttribute("nazev_uctu");
            nazev_uctu.Value = dt.Rows[1]["value"].ToString();
            VetaU.Attributes.Append(nazev_uctu);

            XmlAttribute nazev_bank = xml.CreateAttribute("nazev_bank");
            nazev_bank.Value = dt.Rows[9]["value"].ToString();
            VetaU.Attributes.Append(nazev_bank);

            XmlAttribute ulice1 = xml.CreateAttribute("ulice");

            ulice1.Value = dt.Rows[10]["value"].ToString().Split(',')[1];
            VetaU.Attributes.Append(ulice1);
            XmlAttribute obec = xml.CreateAttribute("obec");
            obec.Value = dt.Rows[10]["value"].ToString().Split(',')[2].Substring(6, dt.Rows[10]["value"].ToString().Split(',')[2].Length - 6);
            VetaU.Attributes.Append(obec);
            XmlAttribute psc2 = xml.CreateAttribute("psc");
            psc2.Value = dt.Rows[10]["value"].ToString().Split(',')[2].Substring(1, 6);
            VetaU.Attributes.Append(psc2);

            XmlAttribute stat2 = xml.CreateAttribute("stat");
            stat2.Value = dt.Rows[10]["value"].ToString().Split(',')[3];
            VetaU.Attributes.Append(stat2);
            firstchild.AppendChild(VetaU);

            XmlNode VetaUV = xml.CreateElement("VetaUV");

            XmlAttribute typ_uctu1 = xml.CreateAttribute("typ_uctu");
            typ_uctu1.Value = "Z";
            VetaUV.Attributes.Append(typ_uctu1);
            XmlAttribute c_uctu1 = xml.CreateAttribute("c_uctu");
            c_uctu1.Value = dt.Rows[14]["value"].ToString();
            VetaUV.Attributes.Append(c_uctu1);
            XmlAttribute mena1 = xml.CreateAttribute("mena");
            mena1.Value = dt.Rows[15]["value"].ToString();
            VetaUV.Attributes.Append(mena1);
            XmlAttribute typ_idbank1 = xml.CreateAttribute("typ_idbank");
            typ_idbank1.Value = "BIC";
            VetaUV.Attributes.Append(typ_idbank1);
            XmlAttribute id_bank1 = xml.CreateAttribute("id_bank");
            id_bank1.Value = dt.Rows[13]["value"].ToString();
            VetaUV.Attributes.Append(id_bank1);
            XmlAttribute nazev_uctu1 = xml.CreateAttribute("nazev_uctu");
            nazev_uctu1.Value = dt.Rows[1]["value"].ToString();
            VetaUV.Attributes.Append(nazev_uctu1);
            XmlAttribute nazev_bank1 = xml.CreateAttribute("nazev_bank");
            nazev_bank1.Value = dt.Rows[9]["value"].ToString();
            VetaUV.Attributes.Append(nazev_bank1);
            XmlAttribute ulice2 = xml.CreateAttribute("ulice");
            ulice2.Value = dt.Rows[10]["value"].ToString().Split(',')[1];
            VetaUV.Attributes.Append(ulice2);
            XmlAttribute obec1 = xml.CreateAttribute("obec");
            obec1.Value = dt.Rows[10]["value"].ToString().Split(',')[2].Substring(6, dt.Rows[10]["value"].ToString().Split(',')[2].Length - 6);
            VetaUV.Attributes.Append(obec1);
            XmlAttribute psc3 = xml.CreateAttribute("psc");
            psc3.Value = dt.Rows[10]["value"].ToString().Split(',')[2].Substring(1, 6);
            VetaUV.Attributes.Append(psc3);
            XmlAttribute stat3 = xml.CreateAttribute("stat");
            stat3.Value = dt.Rows[10]["value"].ToString().Split(',')[3];
            VetaUV.Attributes.Append(stat3);
            firstchild.AppendChild(VetaUV);

            XmlNode VetaE = xml.CreateElement("VetaE");

            XmlAttribute duvreg = xml.CreateAttribute("duvreg");
            duvreg.Value = "§ 6c odst. 2";
            VetaE.Attributes.Append(duvreg);


            XmlAttribute duv_dobrreg = xml.CreateAttribute("duv_dobrreg");
            duv_dobrreg.Value = "bude využívat sklad Amazon v České republice";
            VetaE.Attributes.Append(duv_dobrreg);


            XmlAttribute obrat_dobr = xml.CreateAttribute("obrat_dobr");
            obrat_dobr.Value = "9000";
            VetaE.Attributes.Append(obrat_dobr);


            XmlAttribute k_stat = xml.CreateAttribute("k_stat");
            k_stat.Value = "  ";
            VetaE.Attributes.Append(k_stat);

            firstchild.AppendChild(VetaE);


            XmlNode VetaR = xml.CreateElement("VetaR");
            XmlAttribute poradi = xml.CreateAttribute("poradi");
            poradi.Value = "1";
            VetaR.Attributes.Append(poradi);

            XmlAttribute t_prilohy = xml.CreateAttribute("t_prilohy");
            t_prilohy.Value = "Popis činnosti společnosti";
            VetaR.Attributes.Append(t_prilohy);
            firstchild.AppendChild(VetaR);

            XmlNode VetaR1 = xml.CreateElement("VetaR");
            XmlAttribute poradi1 = xml.CreateAttribute("poradi");
            poradi1.Value = "2";
            VetaR1.Attributes.Append(poradi1);

            XmlAttribute t_prilohy1 = xml.CreateAttribute("t_prilohy");
            t_prilohy1.Value = "Společnost provozuje některé obchodní aktivity v České republice";
            VetaR1.Attributes.Append(t_prilohy1);
            firstchild.AppendChild(VetaR1);
            XmlNode VetaR2 = xml.CreateElement("VetaR");
            XmlAttribute poradi2 = xml.CreateAttribute("poradi");
            poradi2.Value = "3";
            VetaR2.Attributes.Append(poradi2);

            XmlAttribute t_prilohy2 = xml.CreateAttribute("t_prilohy");
            t_prilohy2.Value = "prostřednictvím programu Amazon PAN-EU FBA s cílem mít český sklad a";
            VetaR2.Attributes.Append(t_prilohy2);

            firstchild.AppendChild(VetaR2);
            XmlNode VetaR3 = xml.CreateElement("VetaR");
            XmlAttribute poradi3 = xml.CreateAttribute("poradi");
            poradi3.Value = "4";
            VetaR3.Attributes.Append(poradi3);

            XmlAttribute t_prilohy3 = xml.CreateAttribute("t_prilohy");
            t_prilohy3.Value = "zasílat výrobky z České republiky do jiných zemí EU, jako je Německo,";
            VetaR3.Attributes.Append(t_prilohy3);
            firstchild.AppendChild(VetaR3);
            XmlNode VetaR4 = xml.CreateElement("VetaR");
            XmlAttribute poradi4 = xml.CreateAttribute("poradi");
            poradi4.Value = "5";
            VetaR4.Attributes.Append(poradi4);

            XmlAttribute t_prilohy4 = xml.CreateAttribute("t_prilohy");
            t_prilohy4.Value = "Francie (být prodejcem Amazonu)";
            VetaR4.Attributes.Append(t_prilohy4);

            firstchild.AppendChild(VetaR4);
            XmlNode VetaR5 = xml.CreateElement("VetaR");
            XmlAttribute poradi5 = xml.CreateAttribute("poradi");
            poradi5.Value = "6";
            VetaR5.Attributes.Append(poradi5);

            XmlAttribute t_prilohy5 = xml.CreateAttribute("t_prilohy");
            t_prilohy5.Value = "";
            VetaR5.Attributes.Append(t_prilohy5);

            firstchild.AppendChild(VetaR5);
            XmlNode VetaR6 = xml.CreateElement("VetaR");
            XmlAttribute poradi6 = xml.CreateAttribute("poradi");
            poradi6.Value = "7";
            VetaR6.Attributes.Append(poradi6);

            XmlAttribute t_prilohy6 = xml.CreateAttribute("t_prilohy");
            t_prilohy6.Value = "Popis přílohy formuláře žádosti";
            VetaR6.Attributes.Append(t_prilohy6);

            firstchild.AppendChild(VetaR6);
            XmlNode VetaR7 = xml.CreateElement("VetaR");
            XmlAttribute poradi7 = xml.CreateAttribute("poradi");
            poradi7.Value = "8";
            VetaR7.Attributes.Append(poradi7);

            XmlAttribute t_prilohy7 = xml.CreateAttribute("t_prilohy");
            t_prilohy7.Value = "ID = Občanský průkaz zákonného zástupce";
            VetaR7.Attributes.Append(t_prilohy7);
            firstchild.AppendChild(VetaR7);

            XmlNode VetaR8 = xml.CreateElement("VetaR");
            XmlAttribute poradi8 = xml.CreateAttribute("poradi");
            poradi8.Value = "9";
            VetaR8.Attributes.Append(poradi8);

            XmlAttribute t_prilohy8 = xml.CreateAttribute("t_prilohy");
            t_prilohy8.Value = "PP= cestovní pas Zákonný zástupce";
            VetaR8.Attributes.Append(t_prilohy8);

            firstchild.AppendChild(VetaR8);
            XmlNode VetaR9 = xml.CreateElement("VetaR");
            XmlAttribute poradi9 = xml.CreateAttribute("poradi");
            poradi9.Value = "10";
            VetaR9.Attributes.Append(poradi9);

            XmlAttribute t_prilohy9 = xml.CreateAttribute("t_prilohy");
            t_prilohy9.Value = "CC = živnostenské oprávnění";
            VetaR9.Attributes.Append(t_prilohy9);
            firstchild.AppendChild(VetaR9);

            XmlNode VetaR10 = xml.CreateElement("VetaR");
            XmlAttribute poradi10 = xml.CreateAttribute("poradi");
            poradi10.Value = "11";
            VetaR10.Attributes.Append(poradi10);

            XmlAttribute t_prilohy10 = xml.CreateAttribute("t_prilohy");
            t_prilohy10.Value = "CCT = Překlad obchodní licence";
            VetaR10.Attributes.Append(t_prilohy10);
            firstchild.AppendChild(VetaR10);

            XmlNode VetaR11 = xml.CreateElement("VetaR");
            XmlAttribute poradi11 = xml.CreateAttribute("poradi");
            poradi11.Value = "12";
            VetaR11.Attributes.Append(poradi11);

            XmlAttribute t_prilohy11 = xml.CreateAttribute("t_prilohy");
            t_prilohy11.Value = "POA = podepsaná plná moc";
            VetaR11.Attributes.Append(t_prilohy11);
            firstchild.AppendChild(VetaR11);
            XmlNode VetaR12 = xml.CreateElement("VetaR");
            XmlAttribute poradi12 = xml.CreateAttribute("poradi");
            poradi12.Value = "13";
            VetaR12.Attributes.Append(poradi12);

            XmlAttribute t_prilohy12 = xml.CreateAttribute("t_prilohy");
            t_prilohy12.Value = "ABA = Snímek obrazovky účtu prodejce Amazon";
            VetaR12.Attributes.Append(t_prilohy12);
            firstchild.AppendChild(VetaR12);
            XmlNode VetaR13 = xml.CreateElement("VetaR");
            XmlAttribute poradi13 = xml.CreateAttribute("poradi");
            poradi13.Value = "14";
            VetaR13.Attributes.Append(poradi13);

            XmlAttribute t_prilohy13 = xml.CreateAttribute("t_prilohy");
            t_prilohy13.Value = "BS = Snímek obrazovky bankovního účtu nebo bankovní výpis";
            VetaR13.Attributes.Append(t_prilohy13);

            firstchild.AppendChild(VetaR13);
            XmlNode VetaR14 = xml.CreateElement("VetaR");
            XmlAttribute poradi14 = xml.CreateAttribute("poradi");
            poradi14.Value = "15";
            VetaR14.Attributes.Append(poradi14);

            XmlAttribute t_prilohy14 = xml.CreateAttribute("t_prilohy");
            t_prilohy14.Value = "DE (*) VAT = DE (*) Osvědčení/dopis k DPH";
            VetaR14.Attributes.Append(t_prilohy14);

            firstchild.AppendChild(VetaR14);

            #endregion

            //把根节点加到xml文档中            
            xml.AppendChild(rootCode);
            xml.InsertBefore(Declaration, xml.DocumentElement);
            string filenamewithoutExtension = Path.GetFileNameWithoutExtension(filepath);
            string lastElement = filenamewithoutExtension.Split('-').Last();
            string generatefilepath = "../../" + newexcelpath;

            if (!Directory.Exists(generatefilepath))
            {
                Directory.CreateDirectory(generatefilepath);
            }
            string filename = "BJTY-" + DateTime.Now.ToString("yyyyMMdd") + "-" + lastElement + ".xml";


            //最后将整个xml文件保存在D盘             
            xml.Save(generatefilepath + filename);
        }


        /// <summary>
        /// 创建xml并且写入
        /// </summary>
        public static void CreateCnXmlDoc(string filepath, DataTable dt)
        {


            //首先创建 XmlDocument xml文档 
            XmlDocument xml = new XmlDocument();
            //创建Xml声明部分，即<?xml version="1.0" encoding="utf-8" ?>
            XmlDeclaration Declaration = xml.CreateXmlDeclaration("1.0", "utf-8", null);
            #region 节点
            //创建根节点 config           
            XmlNode rootCode = xml.CreateElement("Pisemnost");
            XmlAttribute verzeSW = xml.CreateAttribute("verzeSW");
            verzeSW.Value = "41.34.3";
            XmlAttribute nazevSW = xml.CreateAttribute("nazevSW");
            nazevSW.Value = "EPO MF ČR";
            rootCode.Attributes.Append(verzeSW);
            rootCode.Attributes.Append(nazevSW);
            XmlNode firstchild = xml.CreateElement("IRSPH1");
            XmlAttribute verzePis = xml.CreateAttribute("verzePis");
            verzePis.Value = "02.03";
            firstchild.Attributes.Append(verzePis);
            rootCode.AppendChild(firstchild);
            XmlNode VetaD = xml.CreateElement("VetaD");
            XmlAttribute dokument = xml.CreateAttribute("dokument");
            dokument.Value = "PH1";
            VetaD.Attributes.Append(dokument);
            XmlAttribute k_uladis = xml.CreateAttribute("k_uladis");
            k_uladis.Value = "IRS";
            VetaD.Attributes.Append(k_uladis);
            XmlAttribute typ_platce = xml.CreateAttribute("typ_platce");
            typ_platce.Value = "T";
            VetaD.Attributes.Append(typ_platce);
            firstchild.AppendChild(VetaD);

            XmlNode VetaP = xml.CreateElement("VetaP");
            XmlAttribute zkrobchjm = xml.CreateAttribute("zkrobchjm");
            zkrobchjm.Value = dt.Rows[1]["value"].ToString();
            VetaP.Attributes.Append(zkrobchjm);
            XmlAttribute c_pop = xml.CreateAttribute("c_pop");
            c_pop.Value = dt.Rows[6]["value"].ToString().Split(',')[0].Substring(0, 4);
            VetaP.Attributes.Append(c_pop);
            XmlAttribute c_ufo = xml.CreateAttribute("c_ufo");
            c_ufo.Value = "463";
            VetaP.Attributes.Append(c_ufo);
            XmlAttribute c_pracufo = xml.CreateAttribute("c_pracufo");
            c_pracufo.Value = "3201";
            VetaP.Attributes.Append(c_pracufo);
            XmlAttribute dic = xml.CreateAttribute("dic");
            dic.Value = "9999999999";
            VetaP.Attributes.Append(dic);
            XmlAttribute naz_obce = xml.CreateAttribute("naz_obce");
            naz_obce.Value = dt.Rows[6]["value"].ToString().Split(',')[2].Substring(1, dt.Rows[6]["value"].ToString().Split(',')[2].Length - 11);
            VetaP.Attributes.Append(naz_obce);
            XmlAttribute opr_jmeno = xml.CreateAttribute("opr_jmeno");
            opr_jmeno.Value = "Tianyou";
            VetaP.Attributes.Append(opr_jmeno);
            XmlAttribute opr_postaveni = xml.CreateAttribute("opr_postaveni");
            opr_postaveni.Value = "Zákonný zástupce pověřeného agent";
            VetaP.Attributes.Append(opr_postaveni);
            XmlAttribute opr_prijmeni = xml.CreateAttribute("opr_prijmeni");
            opr_prijmeni.Value = "Zhou";
            VetaP.Attributes.Append(opr_prijmeni);
            XmlAttribute psc = xml.CreateAttribute("psc");
            psc.Value = dt.Rows[6]["value"].ToString().Split(',')[2].Substring(dt.Rows[6]["value"].ToString().Split(',')[2].Length - 9).Substring(0, dt.Rows[6]["value"].ToString().Split(',')[2].Substring(dt.Rows[6]["value"].ToString().Split(',')[2].Length - 9).Length - 3);
            VetaP.Attributes.Append(psc);
            XmlAttribute stat = xml.CreateAttribute("stat");
            stat.Value = dt.Rows[6]["value"].ToString().Split(',')[2].Substring(dt.Rows[6]["value"].ToString().Split(',')[2].Length - 2, 2);
            VetaP.Attributes.Append(stat);
            XmlAttribute typ_ds = xml.CreateAttribute("typ_ds");
            typ_ds.Value = "P";
            VetaP.Attributes.Append(typ_ds);
            XmlAttribute ulice = xml.CreateAttribute("ulice");
            ulice.Value = dt.Rows[6]["value"].ToString().Split(',')[0].Substring(5, dt.Rows[6]["value"].ToString().Split(',')[0].Length - 5) + "," + dt.Rows[6]["value"].ToString().Split(',')[1];
            VetaP.Attributes.Append(ulice);
            XmlAttribute zast_ic = xml.CreateAttribute("zast_ic");
            zast_ic.Value = "9999999999";
            VetaP.Attributes.Append(zast_ic);
            XmlAttribute zast_kod = xml.CreateAttribute("zast_kod");
            zast_kod.Value = "4a";
            VetaP.Attributes.Append(zast_kod);
            XmlAttribute zast_nazev = xml.CreateAttribute("zast_nazev");
            zast_nazev.Value = "Beijing Tianyouyida Management Consulting Ltd Co";
            VetaP.Attributes.Append(zast_nazev);
            XmlAttribute zast_typ = xml.CreateAttribute("zast_typ");
            zast_typ.Value = "P";
            VetaP.Attributes.Append(zast_typ);

            firstchild.AppendChild(VetaP);

            XmlNode VetaA = xml.CreateElement("VetaA");
            XmlAttribute naz_ul = xml.CreateAttribute("naz_ul");
            naz_ul.Value = dt.Rows[6]["value"].ToString().Split(',')[0].Substring(5, dt.Rows[6]["value"].ToString().Split(',')[0].Length - 5) + "," + dt.Rows[6]["value"].ToString().Split(',')[1];
            VetaA.Attributes.Append(naz_ul);


            XmlAttribute c_dom = xml.CreateAttribute("c_dom");
            c_dom.Value = dt.Rows[6]["value"].ToString().Split(',')[0].Substring(0, 4);
            VetaA.Attributes.Append(c_dom);

            XmlAttribute naz_obce1 = xml.CreateAttribute("naz_obce");
            naz_obce.Value = dt.Rows[6]["value"].ToString().Split(',')[2].Substring(1, dt.Rows[6]["value"].ToString().Split(',')[2].Length - 11);
            VetaA.Attributes.Append(naz_obce1);

            XmlAttribute psc1 = xml.CreateAttribute("psc");
            psc1.Value = dt.Rows[6]["value"].ToString().Split(',')[2].Substring(dt.Rows[6]["value"].ToString().Split(',')[2].Length - 9).Substring(0, dt.Rows[6]["value"].ToString().Split(',')[2].Substring(dt.Rows[6]["value"].ToString().Split(',')[2].Length - 9).Length - 3);
            VetaA.Attributes.Append(psc1);

            XmlAttribute stat1 = xml.CreateAttribute("stat");
            stat1.Value = dt.Rows[6]["value"].ToString().Split(',')[2].Substring(dt.Rows[6]["value"].ToString().Split(',')[2].Length - 2, 2);
            VetaA.Attributes.Append(stat1);

            XmlAttribute email = xml.CreateAttribute("email");
            email.Value = " ";
            VetaA.Attributes.Append(email);

            XmlAttribute c_telef = xml.CreateAttribute("c_telef");
            c_telef.Value = " ";
            VetaA.Attributes.Append(c_telef); 
            firstchild.AppendChild(VetaA);



            XmlNode VetaU = xml.CreateElement("VetaU");
            XmlAttribute poradi_uctu = xml.CreateAttribute("poradi_uctu");
            poradi_uctu.Value = "1";
            VetaU.Attributes.Append(poradi_uctu);


            XmlAttribute typ_uctu = xml.CreateAttribute("typ_uctu");
            typ_uctu.Value = "Z";
            VetaU.Attributes.Append(typ_uctu);

            XmlAttribute c_uctu = xml.CreateAttribute("c_uctu");
            c_uctu.Value = dt.Rows[14]["value"].ToString();
            VetaU.Attributes.Append(c_uctu);

            XmlAttribute mena = xml.CreateAttribute("mena");
            mena.Value = dt.Rows[15]["value"].ToString();
            VetaU.Attributes.Append(mena);

            XmlAttribute typ_idbank = xml.CreateAttribute("typ_idbank");
            typ_idbank.Value = "SC";
            VetaU.Attributes.Append(typ_idbank);

            XmlAttribute id_bank = xml.CreateAttribute("id_bank");
            id_bank.Value = dt.Rows[13]["value"].ToString();
            VetaU.Attributes.Append(id_bank);

            XmlAttribute verejny = xml.CreateAttribute("verejny");
            verejny.Value = "N";
            VetaU.Attributes.Append(verejny);

            XmlAttribute nazev_uctu = xml.CreateAttribute("nazev_uctu");
            nazev_uctu.Value = dt.Rows[1]["value"].ToString();
            VetaU.Attributes.Append(nazev_uctu);

            XmlAttribute nazev_bank = xml.CreateAttribute("nazev_bank");
            nazev_bank.Value = dt.Rows[9]["value"].ToString();
            VetaU.Attributes.Append(nazev_bank);

            XmlAttribute ulice1 = xml.CreateAttribute("ulice");

            ulice1.Value = dt.Rows[10]["value"].ToString().Split(',')[1];
            VetaU.Attributes.Append(ulice1);
            XmlAttribute obec = xml.CreateAttribute("obec");
            obec.Value = dt.Rows[10]["value"].ToString().Split(',')[2].Substring(6, dt.Rows[10]["value"].ToString().Split(',')[2].Length - 6);
            VetaU.Attributes.Append(obec);
            XmlAttribute psc2 = xml.CreateAttribute("psc");
            psc2.Value = dt.Rows[10]["value"].ToString().Split(',')[2].Substring(1, 6);
            VetaU.Attributes.Append(psc2);

            XmlAttribute stat2 = xml.CreateAttribute("stat");
            stat2.Value = dt.Rows[10]["value"].ToString().Split(',')[3];
            VetaU.Attributes.Append(stat2);
            firstchild.AppendChild(VetaU);

            XmlNode VetaUV = xml.CreateElement("VetaUV");

            XmlAttribute typ_uctu1 = xml.CreateAttribute("typ_uctu");
            typ_uctu1.Value = "Z";
            VetaUV.Attributes.Append(typ_uctu1);
            XmlAttribute c_uctu1 = xml.CreateAttribute("c_uctu");
            c_uctu1.Value = dt.Rows[14]["value"].ToString();
            VetaUV.Attributes.Append(c_uctu1);
            XmlAttribute mena1 = xml.CreateAttribute("mena");
            mena1.Value = dt.Rows[15]["value"].ToString();
            VetaUV.Attributes.Append(mena1);
            XmlAttribute typ_idbank1 = xml.CreateAttribute("typ_idbank");
            typ_idbank1.Value = "BIC";
            VetaUV.Attributes.Append(typ_idbank1);
            XmlAttribute id_bank1 = xml.CreateAttribute("id_bank");
            id_bank1.Value = dt.Rows[13]["value"].ToString();
            VetaUV.Attributes.Append(id_bank1);
            XmlAttribute nazev_uctu1 = xml.CreateAttribute("nazev_uctu");
            nazev_uctu1.Value = dt.Rows[1]["value"].ToString();
            VetaUV.Attributes.Append(nazev_uctu1);
            XmlAttribute nazev_bank1 = xml.CreateAttribute("nazev_bank");
            nazev_bank1.Value = dt.Rows[9]["value"].ToString();
            VetaUV.Attributes.Append(nazev_bank1);
            XmlAttribute ulice2 = xml.CreateAttribute("ulice");
            ulice2.Value = dt.Rows[10]["value"].ToString().Split(',')[1];
            VetaUV.Attributes.Append(ulice2);
            XmlAttribute obec1 = xml.CreateAttribute("obec");
            obec1.Value = dt.Rows[10]["value"].ToString().Split(',')[2].Substring(6, dt.Rows[10]["value"].ToString().Split(',')[2].Length - 6);
            VetaUV.Attributes.Append(obec1);
            XmlAttribute psc3 = xml.CreateAttribute("psc");
            psc3.Value = dt.Rows[10]["value"].ToString().Split(',')[2].Substring(1, 6);
            VetaUV.Attributes.Append(psc3);
            XmlAttribute stat3 = xml.CreateAttribute("stat");
            stat3.Value = dt.Rows[10]["value"].ToString().Split(',')[3];
            VetaUV.Attributes.Append(stat3);
            firstchild.AppendChild(VetaUV);

            XmlNode VetaE = xml.CreateElement("VetaE");

            XmlAttribute duvreg = xml.CreateAttribute("duvreg");
            duvreg.Value = "§ 6c odst. 2";
            VetaE.Attributes.Append(duvreg);


            XmlAttribute duv_dobrreg = xml.CreateAttribute("duv_dobrreg");
            duv_dobrreg.Value = "bude využívat sklad Amazon v České republice";
            VetaE.Attributes.Append(duv_dobrreg);


            XmlAttribute obrat_dobr = xml.CreateAttribute("obrat_dobr");
            obrat_dobr.Value = "9000";
            VetaE.Attributes.Append(obrat_dobr);


            XmlAttribute k_stat = xml.CreateAttribute("k_stat");
            k_stat.Value = "  ";
            VetaE.Attributes.Append(k_stat);

            firstchild.AppendChild(VetaE);


            XmlNode VetaR = xml.CreateElement("VetaR");
            XmlAttribute poradi = xml.CreateAttribute("poradi");
            poradi.Value = "1";
            VetaR.Attributes.Append(poradi);

            XmlAttribute t_prilohy = xml.CreateAttribute("t_prilohy");
            t_prilohy.Value = "Popis činnosti společnosti";
            VetaR.Attributes.Append(t_prilohy);
            firstchild.AppendChild(VetaR);

            XmlNode VetaR1 = xml.CreateElement("VetaR");
            XmlAttribute poradi1 = xml.CreateAttribute("poradi");
            poradi1.Value = "2";
            VetaR1.Attributes.Append(poradi1);

            XmlAttribute t_prilohy1 = xml.CreateAttribute("t_prilohy");
            t_prilohy1.Value = "Společnost provozuje některé obchodní aktivity v České republice";
            VetaR1.Attributes.Append(t_prilohy1);
            firstchild.AppendChild(VetaR1);
            XmlNode VetaR2 = xml.CreateElement("VetaR");
            XmlAttribute poradi2 = xml.CreateAttribute("poradi");
            poradi2.Value = "3";
            VetaR2.Attributes.Append(poradi2);

            XmlAttribute t_prilohy2 = xml.CreateAttribute("t_prilohy");
            t_prilohy2.Value = "prostřednictvím programu Amazon PAN-EU FBA s cílem mít český sklad a";
            VetaR2.Attributes.Append(t_prilohy2);

            firstchild.AppendChild(VetaR2);
            XmlNode VetaR3 = xml.CreateElement("VetaR");
            XmlAttribute poradi3 = xml.CreateAttribute("poradi");
            poradi3.Value = "4";
            VetaR3.Attributes.Append(poradi3);

            XmlAttribute t_prilohy3 = xml.CreateAttribute("t_prilohy");
            t_prilohy3.Value = "zasílat výrobky z České republiky do jiných zemí EU, jako je Německo,";
            VetaR3.Attributes.Append(t_prilohy3);
            firstchild.AppendChild(VetaR3);
            XmlNode VetaR4 = xml.CreateElement("VetaR");
            XmlAttribute poradi4 = xml.CreateAttribute("poradi");
            poradi4.Value = "5";
            VetaR4.Attributes.Append(poradi4);

            XmlAttribute t_prilohy4 = xml.CreateAttribute("t_prilohy");
            t_prilohy4.Value = "Francie (být prodejcem Amazonu)";
            VetaR4.Attributes.Append(t_prilohy4);

            firstchild.AppendChild(VetaR4);
            XmlNode VetaR5 = xml.CreateElement("VetaR");
            XmlAttribute poradi5 = xml.CreateAttribute("poradi");
            poradi5.Value = "6";
            VetaR5.Attributes.Append(poradi5);

            XmlAttribute t_prilohy5 = xml.CreateAttribute("t_prilohy");
            t_prilohy5.Value = "";
            VetaR5.Attributes.Append(t_prilohy5);

            firstchild.AppendChild(VetaR5);
            XmlNode VetaR6 = xml.CreateElement("VetaR");
            XmlAttribute poradi6 = xml.CreateAttribute("poradi");
            poradi6.Value = "7";
            VetaR6.Attributes.Append(poradi6);

            XmlAttribute t_prilohy6 = xml.CreateAttribute("t_prilohy");
            t_prilohy6.Value = "Popis přílohy formuláře žádosti";
            VetaR6.Attributes.Append(t_prilohy6);

            firstchild.AppendChild(VetaR6);
            XmlNode VetaR7 = xml.CreateElement("VetaR");
            XmlAttribute poradi7 = xml.CreateAttribute("poradi");
            poradi7.Value = "8";
            VetaR7.Attributes.Append(poradi7);

            XmlAttribute t_prilohy7 = xml.CreateAttribute("t_prilohy");
            t_prilohy7.Value = "ID = Občanský průkaz zákonného zástupce";
            VetaR7.Attributes.Append(t_prilohy7);
            firstchild.AppendChild(VetaR7);

            XmlNode VetaR8 = xml.CreateElement("VetaR");
            XmlAttribute poradi8 = xml.CreateAttribute("poradi");
            poradi8.Value = "9";
            VetaR8.Attributes.Append(poradi8);

            XmlAttribute t_prilohy8 = xml.CreateAttribute("t_prilohy");
            t_prilohy8.Value = "PP= cestovní pas Zákonný zástupce";
            VetaR8.Attributes.Append(t_prilohy8);

            firstchild.AppendChild(VetaR8);
            XmlNode VetaR9 = xml.CreateElement("VetaR");
            XmlAttribute poradi9 = xml.CreateAttribute("poradi");
            poradi9.Value = "10";
            VetaR9.Attributes.Append(poradi9);

            XmlAttribute t_prilohy9 = xml.CreateAttribute("t_prilohy");
            t_prilohy9.Value = "CC = živnostenské oprávnění";
            VetaR9.Attributes.Append(t_prilohy9);
            firstchild.AppendChild(VetaR9);

            XmlNode VetaR10 = xml.CreateElement("VetaR");
            XmlAttribute poradi10 = xml.CreateAttribute("poradi");
            poradi10.Value = "11";
            VetaR10.Attributes.Append(poradi10);

            XmlAttribute t_prilohy10 = xml.CreateAttribute("t_prilohy");
            t_prilohy10.Value = "CCT = Překlad obchodní licence";
            VetaR10.Attributes.Append(t_prilohy10);
            firstchild.AppendChild(VetaR10);

            XmlNode VetaR11 = xml.CreateElement("VetaR");
            XmlAttribute poradi11 = xml.CreateAttribute("poradi");
            poradi11.Value = "12";
            VetaR11.Attributes.Append(poradi11);

            XmlAttribute t_prilohy11 = xml.CreateAttribute("t_prilohy");
            t_prilohy11.Value = "POA = podepsaná plná moc";
            VetaR11.Attributes.Append(t_prilohy11);
            firstchild.AppendChild(VetaR11);
            XmlNode VetaR12 = xml.CreateElement("VetaR");
            XmlAttribute poradi12 = xml.CreateAttribute("poradi");
            poradi12.Value = "13";
            VetaR12.Attributes.Append(poradi12);

            XmlAttribute t_prilohy12 = xml.CreateAttribute("t_prilohy");
            t_prilohy12.Value = "ABA = Snímek obrazovky účtu prodejce Amazon";
            VetaR12.Attributes.Append(t_prilohy12);
            firstchild.AppendChild(VetaR12);
            XmlNode VetaR13 = xml.CreateElement("VetaR");
            XmlAttribute poradi13 = xml.CreateAttribute("poradi");
            poradi13.Value = "14";
            VetaR13.Attributes.Append(poradi13);

            XmlAttribute t_prilohy13 = xml.CreateAttribute("t_prilohy");
            t_prilohy13.Value = "BS = Snímek obrazovky bankovního účtu nebo bankovní výpis";
            VetaR13.Attributes.Append(t_prilohy13);

            firstchild.AppendChild(VetaR13);
            XmlNode VetaR14 = xml.CreateElement("VetaR");
            XmlAttribute poradi14 = xml.CreateAttribute("poradi");
            poradi14.Value = "15";
            VetaR14.Attributes.Append(poradi14);

            XmlAttribute t_prilohy14 = xml.CreateAttribute("t_prilohy");
            t_prilohy14.Value = "DE (*) VAT = DE (*) Osvědčení/dopis k DPH";
            VetaR14.Attributes.Append(t_prilohy14);

            firstchild.AppendChild(VetaR14);

            #endregion

            //把根节点加到xml文档中            
            xml.AppendChild(rootCode);
            xml.InsertBefore(Declaration, xml.DocumentElement);
            string filenamewithoutExtension = Path.GetFileNameWithoutExtension(filepath);
            string lastElement = filenamewithoutExtension.Split('-').Last();
            string generatefilepath = "../../" + newexcelpath;

            if (!Directory.Exists(generatefilepath))
            {
                Directory.CreateDirectory(generatefilepath);
            }
            string filename = "BJTY-" + DateTime.Now.ToString("yyyyMMdd") + "-" + lastElement + ".xml";


            //最后将整个xml文件保存在D盘             
            xml.Save(generatefilepath + filename);
        }

        /// <summary>
        /// 根据国家名称获取两位国家代码
        /// </summary>
        public static void GetCountryCodeByNamne(string name)
        {

        }
        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static string ReadFromExcelFile(string filePath)
        {
            string json = "[";
            IWorkbook wk = null;
            string extension = System.IO.Path.GetExtension(filePath);
            try
            {
                FileStream fs = File.OpenRead(filePath);
                if (extension.Equals(".xls"))
                {
                    //把xls文件中的数据写入wk中
                    wk = new HSSFWorkbook(fs);
                }
                else
                {
                    //把xlsx文件中的数据写入wk中
                    wk = new XSSFWorkbook(fs);
                }

                fs.Close();
                //读取当前表数据
                ISheet sheet = wk.GetSheetAt(0);

                IRow row = sheet.GetRow(0);  //读取当前行数据
                //LastRowNum 是当前表的总行数-1（注意）
                int offset = 0;
                for (int i = 0; i <= sheet.LastRowNum; i++)
                {
                    row = sheet.GetRow(i);  //读取当前行数据
                    if (row != null)
                    {
                        //读取该行的第j列数据 
                        string value = row.GetCell(1).ToString();
                        if (!string.IsNullOrEmpty(value))
                        {
                            json += "{\"key\":\"" + i + "\",\"value\":\"" + value + "\"},";
                        }
                    }

                }
                json = json.TrimEnd(',');
            }

            catch (Exception e)
            {
                //只在Debug模式下才输出
                Console.WriteLine(e.Message);
            }
            json += "]";
            return json;
        }


        /// <summary>
        /// Excel导入成Datable
        /// </summary>
        /// <param name="file">导入路径(包含文件名与扩展名)</param>
        /// <returns></returns>
        public static DataTable ExcelToTable(string file)
        {
            DataTable dt = new DataTable();
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
                if (workbook == null) { return null; }
                ISheet sheet = workbook.GetSheetAt(0);

                //表头  
                IRow header = sheet.GetRow(sheet.FirstRowNum);
                List<int> columns = new List<int>();
                for (int i = 0; i < header.LastCellNum; i++)
                {
                    object obj = GetValueType(header.GetCell(i));
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据  
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow dr = dt.NewRow();
                    bool hasValue = false;
                    foreach (int j in columns)
                    {
                        dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }




        /// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:  
                    return null;
                case CellType.Boolean: //BOOLEAN:  
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:  
                    return cell.NumericCellValue;
                case CellType.String: //STRING:  
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:  
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:  
                default:
                    return "=" + cell.CellFormula;
            }
        }
    }


}
